﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using System.Text.RegularExpressions;

namespace SQL_EG
{
   public class EntityManager
    {
       public string serverName {get;set;}
       public string user {get;set;}
       public string pwd {get;set;}
       private Server myserver {get;set;}

        public EntityManager (string _serverName,string _user, string _pwd)
        {
            serverName = _serverName;
            user = _user; 
            pwd = _pwd;
            myserver = new Server(serverName);
        }
        public EntityManager()
        {
           
        }

       public bool ConnectoServer()
       {
           bool result = false;
           try
           {
               if (myserver.ConnectionContext.IsOpen)
                   myserver.ConnectionContext.Disconnect();

               myserver.ConnectionContext.LoginSecure = false;
               myserver.ConnectionContext.Login = user;
               myserver.ConnectionContext.Password = pwd;
               myserver.ConnectionContext.Connect();
               result = true;
           }
           catch (Exception)
           {              
               result = false;
           }
           return result;
       }

       public async Task<DataTable> GetDefaultInstance()
       {
           return await Task.Run(()=> 
               {
                 var res = SmoApplication.EnumAvailableSqlServers(true);
                 return res;
               });
       }

       public List<string> GetDatabaseList()
       {
         var listDB = new List<string>();

            foreach (Database dbase in myserver.Databases)
                    {
                        if (dbase.IsSystemObject)
                        {
                            continue;
                        }
                      listDB.Add(dbase.Name);

                    }

            return listDB;
       }

           public List<string> GetTableList(string dataBase)
           {
          
                   var listTable = new List<string>();

                   Database bdd = myserver.Databases[dataBase];

                   foreach (Table table in bdd.Tables)
                   {
                       if (table.IsSystemObject)
                       {
                           continue;
                       }
                       listTable.Add(table.Name);
                   }

                   return listTable;

       }

       public async Task<List<string>> GetTableListAsync(string dataBase)
       {
        
               return await Task.Run(() =>
                  {
                      var listTable = new List<string>();

                      Database bdd = myserver.Databases[dataBase];

                      foreach (Table table in bdd.Tables)
                      {
                          if (table.IsSystemObject)
                          {
                              continue;
                          }
                          listTable.Add(table.Name);
                      }

                      return listTable;
                  });
         

       }

       public DataTable GetEnumForeignsKey(string database, string table)
       {
           Table tb = myserver.Databases[database].Tables[table];

           return tb.EnumForeignKeys();
       }

       public List<EntityProperty> GetPropertyList(string database, string tableIndex)
       {
           var list = new List<EntityProperty>();

           try
           {
               Table tb = myserver.Databases[database].Tables[tableIndex];

               if (tb == null)
                   return  new List<EntityProperty>() ;

               foreach (Column column in tb.Columns)
               {
                   if (column.IsForeignKey)
                   {

                   }
                   else
                   {
                       string type = Regex.Replace(EntityProperty.
                                       GetNormalType(EntityProperty.ConvertSqlType(column.DataType.SqlDataType)).Name.ToLower()
                                       , "[0-9]", "");
                       list.Add(new EntityProperty
                       {
                           Name = column.Name,
                           Type = type

                       });
                   }


               }

               foreach (ForeignKey cle in tb.ForeignKeys)
               {
                   foreach (ForeignKeyColumn column in cle.Columns)
                   {
                       list.Add(new EntityProperty
                       {
                           Name = cle.ReferencedTable,
                           Type = GetCorrectStringFormat(cle.ReferencedTable)
                       });
                   }
               }

               var dt = tb.EnumForeignKeys();

               for (var i = 0; i < tb.EnumForeignKeys().Rows.Count; i++)
               {
                   list.Add(new EntityProperty
                   {
                       Name = dt.Rows[i].Field<string>("Table_Name"),
                       Type = "List<" + (GetCorrectStringFormat(dt.Rows[i].Field<string>("Table_Name"))) + ">".ToUpper()
                   });
               }
           }
           catch (Exception)
           {
               
              //
           }
           return list;       
       }

       public static string GetCorrectStringFormat(string value)
       {
           return Regex.Replace(value.ToLower(), @"^\w|_\w",
               (match) => match.Value.Replace("_", "").ToUpper());

       }

    }
}
